/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Vector;

/**
 *
 * @author DELL INSPIRON N5010
 */
public class SalesBill 
{
    private int BillId;
    private String BillName;
    private int CustomerId;
    private int AccountId;
    private java.util.Date BuildDate;
    private String State;
    private double TotalCost;
    private String Unit;
    
   
    public Vector<Vector<Object>> Listtablesalesbill()
    {
        Vector<Vector<Object>> listsalesbill=new Vector<Vector<Object>>();
        try
        {
            Connection con=ConecttoDatabase.getConnection();
            String query="select BillId,BillName,CusstomerName,AccountName,BuildDate,State,Unit,TotalCost from SalesBill t1,Customer t2, Administrative t3 where t1.CustomerId=t2.CustomerId and t1.AccountId=t3.AccountId order by BillId desc ";
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery(query);
            while(rs.next())
                    {
                        Vector<Object> rows=new Vector<Object>();
                        rows.add(rs.getInt("BillId"));
                        rows.add(rs.getString("BillName"));
                        rows.add(rs.getString("CusstomerName"));
                        rows.add(rs.getString("AccountName"));
                        SimpleDateFormat sp=new SimpleDateFormat("MM-dd-yyyy");
                        rows.add(sp.format(rs.getDate("BuildDate")));
                        rows.add(rs.getString("State"));
                        rows.add(rs.getString("Unit"));
                        rows.add(rs.getDouble("TotalCost"));
                        listsalesbill.add(rows);    
                        //i++;
                    }
            //System.out.println(i);
           
        }
        catch(Exception ex)
        {
            listsalesbill=null;
            System.out.println(ex);
        }
      
        return listsalesbill;
    }
      public int geti()
    {
         int i=0;
        Vector<Vector<Object>> listsalesbill=new Vector<Vector<Object>>();
        try
        {
            Connection con=ConecttoDatabase.getConnection();
            String query="select BillId,BillName,CusstomerName,AccountName,BuildDate,State,Unit,TotalCost from SalesBill t1,Customer t2, Administrative t3 where t1.CustomerId=t2.CustomerId and t1.AccountId=t3.AccountId order by BillId desc ";
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery(query);
            while(rs.next())
                    {
                        Vector<Object> rows=new Vector<Object>();
                        rows.add(rs.getInt("BillId"));
                        rows.add(rs.getString("BillName"));
                        rows.add(rs.getString("CusstomerName"));
                        rows.add(rs.getString("AccountName"));
                        SimpleDateFormat sp=new SimpleDateFormat("MM-dd-yyyy");
                        rows.add(sp.format(rs.getDate("BuildDate")));
                        rows.add(rs.getString("State"));
                        rows.add(rs.getString("Unit"));
                        rows.add(rs.getDouble("TotalCost"));
                        listsalesbill.add(rows);    
                        i++;
                    }
            System.out.println(i);
           
        }
        catch(Exception ex)
        {
            i=0;
            System.out.println(ex);
        }
      
        return i;
    }
    public Vector<SalesBill> lissb()
    {
        Vector<SalesBill> lsb=new Vector<SalesBill>();
        try
        {
            Connection con=ConecttoDatabase.getConnection();
            String query="select * from SalesBill";
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery(query);
            while(rs.next())
            {
                SalesBill sb=new SalesBill();
                sb.setBillId(rs.getInt("BillId"));
                sb.setBillName(rs.getString("BillName"));
                sb.setAccountId(rs.getInt("AccountId"));
                sb.setBuildDate(rs.getDate("BuildDate"));
                sb.setCustomerId(rs.getInt("CustomerId"));
                sb.setTotalCost(rs.getDouble("TotalCost"));
                sb.setState(rs.getString("State"));
                sb.setUnit(rs.getString("Unit"));
                lsb.add(sb);
            }
        }
        catch(Exception ex)
        {
            lsb=null;
            System.out.println(ex);
        }
        return lsb;
    }
    /**
     * @return the BillId
     */
    public int getBillId() {
        return BillId;
    }

    /**
     * @param BillId the BillId to set
     */
    public void setBillId(int BillId) {
        this.BillId = BillId;
    }

    /**
     * @return the BillName
     */
    public String getBillName() {
        return BillName;
    }

    /**
     * @param BillName the BillName to set
     */
    public void setBillName(String BillName) {
        this.BillName = BillName;
    }

    /**
     * @return the CustomerId
     */
    public int getCustomerId() {
        return CustomerId;
    }

    /**
     * @param CustomerId the CustomerId to set
     */
    public void setCustomerId(int CustomerId) {
        this.CustomerId = CustomerId;
    }

    /**
     * @return the AccountId
     */
    public int getAccountId() {
        return AccountId;
    }

    /**
     * @param AccountId the AccountId to set
     */
    public void setAccountId(int AccountId) {
        this.AccountId = AccountId;
    }

    

    /**
     * @return the State
     */
    public String getState() {
        return State;
    }

    /**
     * @param State the State to set
     */
    public void setState(String State) {
        this.State = State;
    }

    /**
     * @return the TotalCost
     */
    public double getTotalCost() {
        return TotalCost;
    }

    /**
     * @param TotalCost the TotalCost to set
     */
    public void setTotalCost(double TotalCost) {
        this.TotalCost = TotalCost;
    }

    /**
     * @return the Unit
     */
    public String getUnit() {
        return Unit;
    }

    /**
     * @param Unit the Unit to set
     */
    public void setUnit(String Unit) {
        this.Unit = Unit;
    }
    public boolean Addsalesbill()
    {
      boolean result=true;
      try
      {
          Connection con=ConecttoDatabase.getConnection();
          String query="insert into SalesBill values(?, ?, ?, ?, ?, ?, ?)";
          PreparedStatement ps=con.prepareCall(query);
          ps.setString(1, this.BillName);
          ps.setInt(2, this.CustomerId);
          ps.setInt(3, this.AccountId);
          //java.sql.Date bd=new Date(this.BuildDate.getTime()) ;
          //String bd1=bd.toString();
         // SimpleDateFormat sdf=new SimpleDateFormat("MM-dd-yyyy");
          java.sql.Date jsd=new Date(this.BuildDate.getTime());
         // String jsdd=jsd.toString();
          
          ps.setDate(4,jsd );
          ps.setString(5, this.State);
          ps.setString(6, this.Unit);
          ps.setDouble(7, this.TotalCost);
          result=ps.executeUpdate()>0;
      }
      catch(Exception ex)
      {
          System.out.println(ex);
          result=false;
      }
      return result;
    }

    /**
     * @return the BuildDate
     */
    public java.util.Date getBuildDate() {
        return BuildDate;
    }

    /**
     * @param BuildDate the BuildDate to set
     */
    public void setBuildDate(java.util.Date BuildDate) {
        this.BuildDate = BuildDate;
    }

   
    
}
